CREATE VIEW [dbo].[vGiftsReceivedBase]
  AS
    SELECT
            Activity.ID ID,
            Activity.ORIGINATING_TRANS_NUM OriginalTransaction,
            Activity.ORIGINATING_TRANS_NUM TransactionNumber,
            MAX(Activity.SOURCE_SYSTEM) SourceSystem,
            MAX(Activity.TRANSACTION_DATE) TransactionDate,
        CASE
             WHEN MAX(Activity.[SOURCE_SYSTEM]) = 'FR' THEN MAX(Activity.[EFFECTIVE_DATE])
             ELSE MAX(Activity.[TRANSACTION_DATE]) END AS DateReceived,
            SUM(Activity.AMOUNT) Amount,
            MAX(Activity.ORG_CODE) Fund,
            MAX(Activity.SOURCE_CODE) Appeal,
            MAX(Activity.SOLICITOR_ID) SolicitorID,
            MAX(C.CHECK_NUMBER) CheckNumber,
            (CASE MAX(Cash_Accounts.ACCOUNT_TYPE)
                  WHEN 1 THEN 'Credit Card'
                  WHEN 2 THEN 'In Kind'
                  WHEN 3 THEN 'Debit Card'
                  ELSE 'Cash' END) AS PaymentType,
            MAX(Activity.CAMPAIGN_CODE) Campaign,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),1,4)) AS FiscalYear,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),5,2)) AS FiscalMonth,
           ( CASE WHEN MAX(Activity.[SOURCE_SYSTEM]) = 'DUES' THEN 'Membership'
                   WHEN MAX(Activity.[SOURCE_SYSTEM]) = 'MEETING' THEN 'Event'
                   WHEN MAX(Activity.[SOURCE_SYSTEM]) = 'FR'
                   THEN ( CASE WHEN MAX(Activity.[ACTIVITY_TYPE]) = 'PLEDGE'
                               THEN 'Pledge Payment'
                               WHEN ( MAX(Activity.[ACTIVITY_TYPE]) = 'GIFT'
                                      AND MAX(Cash_Accounts.[ACCOUNT_TYPE]) = 2
                                    ) THEN 'In Kind'
                               ELSE 'Cash'
                          END )
                   ELSE 'Gift'
              END ) AS GiftType,
            MAX(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
            MAX(C.RECEIPT_ID) ReceiptID,
            MAX(C.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
            MAX(C.IS_MATCH_GIFT) IsMatchingGift,
            MAX(C.MEM_TRIB_ID) MemorialID,
            '' AS PledgeID,
            MAX(Activity.ACTION_CODES) ListAs,
            MAX(Activity.UF_4) RequestNumber,
            null AS InstallmentDate,
            MAX(C.MEM_TRIB_NAME_TEXT) MemorialNameText,
            MAX(Activity.Mem_trib_Code) AS MemorialTributeType,
            MAX(CONVERT(VARCHAR(512),Activity.NOTE_2)) AS MemorialTributeMessage,
            MAX(Trans_Notify.NOTIFY_ID ) AS TributeNotificationContactID
     FROM Activity
            INNER JOIN Trans C ON
                  Activity.ORIGINATING_TRANS_NUM = C.TRANS_NUMBER
            INNER JOIN TransWatch ON
                  C.TRANS_NUMBER = TransWatch.TransactionNumber AND
                  C.INVOICE_REFERENCE_NUM = TransWatch.InvoiceNumber
            LEFT OUTER JOIN Cash_Accounts ON
                  C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
            LEFT OUTER JOIN Trans_Notify ON
               Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM
     WHERE
            Activity.ACTIVITY_TYPE = 'GIFT' AND Activity.ID = C.ST_ID AND
            (
                  (Activity.SOURCE_SYSTEM = 'FR' AND C.TRANSACTION_TYPE = 'PAY' AND C.JOURNAL_TYPE = 'IN' AND C.SUB_LINE_NUMBER = 1) OR
                  (Activity.SOURCE_SYSTEM IN ('SC', 'DUES') AND C.TRANSACTION_TYPE = 'DIST' AND C.PRODUCT_CODE = Activity.PRODUCT_CODE)
            )
      GROUP BY Activity.ID, Activity.ORIGINATING_TRANS_NUM,  Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE
UNION
      SELECT
            CASE WHEN Invoice_Lines.ST_ID IS NULL THEN Invoice.BT_ID ELSE Invoice_Lines.ST_ID END    ID,
            P.TRANS_NUMBER OriginalTransaction,
            C.TRANS_NUMBER TransactionNumber,
            MAX(P.SOURCE_SYSTEM) SourceSystem,
            MAX(C.TRANSACTION_DATE)TransactionDate,
            MAX(C.TRANSACTION_DATE) DateReceived,
            (CASE WHEN MAX(C.JOURNAL_TYPE) = 'IN' THEN SUM(C.AMOUNT) ELSE -SUM(C.AMOUNT) END) Amount,
            (CASE WHEN MAX(C.JOURNAL_TYPE) = 'IN' THEN MAX(C.OWNER_ORG_CODE) ELSE MAX(C.GL_ACCT_ORG_CODE) END) Fund,
            MAX(P.SOURCE_CODE)Appeal,
            MAX(P.SOLICITOR_ID)SolicitorID,
            MAX(C.CHECK_NUMBER)CheckNumber,
            (CASE MAX(Cash_Accounts.ACCOUNT_TYPE)
                  WHEN 1 THEN 'Credit Card'
                  WHEN 2 THEN 'In Kind'
                  WHEN 3 THEN 'Debit Card'
                  ELSE 'Cash' END) AS PaymentType,
            MAX(Activity.CAMPAIGN_CODE)Campaign,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),1,4)) AS FiscalYear,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),5,2)) AS FiscalMonth,
            (CASE WHEN MAX(Activity.[ACTIVITY_TYPE]) = 'PLEDGE'
             THEN 'Pledge Payment'
             WHEN ( MAX(Activity.[ACTIVITY_TYPE]) = 'GIFT'
                    AND MAX(Cash_Accounts.[ACCOUNT_TYPE]) = 2
                  ) THEN 'In Kind'
                    ELSE 'Cash'
        END),
            MAX(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
            MAX(C.RECEIPT_ID) ReceiptID,
            MAX(P.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
            MAX(P.IS_MATCH_GIFT) IsMatchingGift,
            MAX(P.MEM_TRIB_ID) MemorialID,
            (CASE WHEN MAX(C.BT_ID)= MAX(C.ST_ID) THEN '' ELSE MAX(C.BT_ID) END) AS PledgeID,
            MAX(Activity.ACTION_CODES) ListAs,
            MAX(Activity.UF_4) RequestNumber,
            MAX(Invoice.INSTALL_BILL_DATE) AS InstallmentDate,
            MAX(P.MEM_TRIB_NAME_TEXT) MemorialNameText,
            MAX(Activity.Mem_trib_Code) AS MemorialTributeType,
            MAX(CONVERT(VARCHAR(512),Activity.NOTE_2)) AS MemorialTributeMessage,
            MAX(Trans_Notify.NOTIFY_ID ) AS TributeNotificationContactID
      FROM Invoice
            INNER JOIN TransWatch ON
                  Invoice.ORIGINATING_TRANS_NUM = TransWatch.TransactionNumber AND
                  Invoice.REFERENCE_NUM = TransWatch.InvoiceNumber
            INNER JOIN Trans P ON
                  Invoice.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER
            INNER JOIN Activity ON
                  P.ACTIVITY_SEQN = Activity.SEQN
            INNER JOIN Trans C ON
                  Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
            LEFT OUTER JOIN Cash_Accounts ON
                  C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
            LEFT OUTER JOIN Invoice_Lines ON
                  C.INVOICE_REFERENCE_NUM = Invoice_Lines.REFERENCE_NUM AND
                  C.INVOICE_LINE_NUM = Invoice_Lines.LINE_NUM
            LEFT OUTER JOIN Trans_Notify ON
               Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM           
      WHERE  
            P.TRANSACTION_TYPE = 'DIST' AND Activity.ACTIVITY_TYPE in ('PLEDGE','GIFT') AND C.IS_FR_ITEM = 1 AND
            (
                  (
                        Activity.ACTIVITY_TYPE = 'PLEDGE' and P.LINE_NUMBER = 1 AND P.SUB_LINE_NUMBER = 1 AND Invoice.SOURCE_SYSTEM = 'FR' AND
                        (
                              (C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE = 'AR') OR
                              (C.JOURNAL_TYPE = 'IN' AND C.TRANSACTION_TYPE = 'PAY')
                        )
                  )
            OR
                  (
                       Activity.ACTIVITY_TYPE = 'PLEDGE' and Invoice.SOURCE_SYSTEM = 'DUES' AND
                        P.PRODUCT_CODE = C.PRODUCT_CODE AND
                        C.JOURNAL_TYPE = 'PAY' AND
                        C.TRANSACTION_TYPE = 'AR' AND
                        P.INVOICE_LINE_NUM = C.INVOICE_LINE_NUM
                  )
            OR
                  (
                        Activity.ACTIVITY_TYPE = 'GIFT' and P.LINE_NUMBER = 1 AND P.SUB_LINE_NUMBER = 1 AND Invoice.SOURCE_SYSTEM = 'FR' AND
                        C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE = 'AR'
                  )
            )
      GROUP BY Invoice_Lines.ST_ID, Invoice.BT_ID, C.TRANS_NUMBER, Invoice.ORIGINATING_TRANS_NUM, C.OWNER_ORG_CODE, C.GL_ACCT_ORG_CODE, Activity.CAMPAIGN_CODE, P.SOURCE_CODE, P.TRANS_NUMBER
UNION
      SELECT
            Activity.ID ID,
            Invoice.ORIGINATING_TRANS_NUM  AS  OriginalTransaction,
            0 AS TransactionNumber,
            MAX(Activity.SOURCE_SYSTEM) SourceSystem,
            MAX(Activity.TRANSACTION_DATE) TransactionDate,
        CASE
             WHEN MAX(Activity.[SOURCE_SYSTEM]) = 'FR' THEN MAX(Activity.[EFFECTIVE_DATE])
             ELSE MAX(Activity.[TRANSACTION_DATE]) END AS DateReceived,
            SUM(Activity.AMOUNT) Amount,
            MAX(Activity.ORG_CODE) Fund,
            MAX(Activity.SOURCE_CODE) Appeal,
            '' AS  SolicitorID,
            '' AS  CheckNumber,
            '' AS PaymentType,
            MAX(Activity.CAMPAIGN_CODE) Campaign,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),1,4)) AS FiscalYear,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),5,2)) AS FiscalMonth,
            'Event' AS GiftType,
            MAX(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
            0 AS ReceiptID,
            0 AS MatchingTransaction,
            0 AS IsMatchingGift,
            '' AS MemorialID,
            '' AS PledgeID,
            '' AS ListAs,
            0 AS RequestNumber,
            null AS InstallmentDate,
            '' AS MemorialNameText,
            MAX(Activity.Mem_trib_Code) AS MemorialTributeType,
            MAX(CONVERT(VARCHAR(512),Activity.NOTE_2)) AS MemorialTributeMessage,
            MAX(Trans_Notify.NOTIFY_ID) AS TributeNotificationContactID
      FROM Activity
            INNER JOIN Trans C ON
                  Activity.ORIGINATING_TRANS_NUM = C.TRANS_NUMBER
            INNER JOIN Invoice ON
                  Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
            INNER JOIN TransWatch ON
                        C.TRANS_NUMBER = TransWatch.TransactionNumber AND
                        C.INVOICE_REFERENCE_NUM = TransWatch.InvoiceNumber
            LEFT OUTER JOIN Trans_Notify ON
               Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM           
     WHERE
             Activity.ACTIVITY_TYPE = 'GIFT' AND
             C.TRANSACTION_TYPE = 'DIST' AND
             (C.PRODUCT_CODE = Activity.PRODUCT_CODE OR C.PRODUCT_CODE LIKE Activity.PRODUCT_CODE +'/%') AND
             Activity.SOURCE_SYSTEM = 'MEETING'
     GROUP BY Activity.ID, C.INVOICE_REFERENCE_NUM, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE, Invoice.ORIGINATING_TRANS_NUM
UNION
      SELECT
            MAX(P.ST_ID) ID,
            MAX(P.TRANS_NUMBER) OriginalTransaction,
            MAX(C.TRANS_NUMBER) TransactionNumber,
            MAX(P.SOURCE_SYSTEM) SourceSystem,
            MAX(C.TRANSACTION_DATE)TransactionDate,
            MAX(C.TRANSACTION_DATE) DateReceived,
            -SUM(C.AMOUNT) Amount,
            (
                  CASE WHEN MAX(C.JOURNAL_TYPE) = 'IN'
                  THEN MAX(C.OWNER_ORG_CODE)
                  ELSE MAX(C.GL_ACCT_ORG_CODE) END
            ) Fund,
            MAX(P.SOURCE_CODE)Appeal,
            MAX(P.SOLICITOR_ID)SolicitorID,
            MAX(C.CHECK_NUMBER)CheckNumber,
            (
                  CASE MAX(Cash_Accounts.ACCOUNT_TYPE)
                  WHEN 1 THEN 'Credit Card'
                  WHEN 2 THEN 'In Kind'
                  WHEN 3 THEN 'Debit Card'
                  ELSE 'Cash' END
            ) PaymentType,
            MAX(Activity.CAMPAIGN_CODE)Campaign,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),1,4)) AS FiscalYear,
            CONVERT(int,SUBSTRING(CONVERT(char(6),MAX(C.FISCAL_PERIOD)),5,2)) AS FiscalMonth,
            'Asterisk Tab Adjustment' AS GiftType,
            MAX(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
            MAX(C.RECEIPT_ID) ReceiptID,
            MAX(P.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
            MAX(P.IS_MATCH_GIFT) IsMatchingGift,
            MAX(P.MEM_TRIB_ID) MemorialID,
            (CASE WHEN MAX(C.BT_ID)= MAX(C.ST_ID) THEN '' ELSE MAX(C.BT_ID) END) AS PledgeID,
            ''  ListAs,
            0 RequestNumber,
            MAX(Invoice.INSTALL_BILL_DATE) AS InstallmentDate,
            MAX(P.MEM_TRIB_NAME_TEXT) AS MemorialNameText,
            MAX(Activity.Mem_trib_Code) AS MemorialTributeType,
            MAX(CONVERT(VARCHAR(512),Activity.NOTE_2)) AS MemorialTributeMessage,
            MAX(Trans_Notify.NOTIFY_ID) AS TributeNotificationContactID
      FROM Invoice
            INNER JOIN TransWatch ON
                  Invoice.ORIGINATING_TRANS_NUM = TransWatch.TransactionNumber  AND
                  Invoice.REFERENCE_NUM = TransWatch.InvoiceNumber
            INNER JOIN Trans C ON
                  Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
            LEFT OUTER JOIN Cash_Accounts ON
                  C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
            INNER JOIN Trans P ON
                  Invoice.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER
            INNER JOIN Activity ON
                  P.ACTIVITY_SEQN = Activity.SEQN
            LEFT OUTER JOIN Trans_Notify ON
               Trans_Notify.TRANS_NUMBER = Activity.ORIGINATING_TRANS_NUM           
      WHERE
            P.LINE_NUMBER = 1  AND P.SUB_LINE_NUMBER = 1 AND P.JOURNAL_TYPE = 'IN' AND
            P.TRANSACTION_TYPE = 'DIST' AND Invoice.SOURCE_SYSTEM='FR' AND
            (
                  (C.JOURNAL_TYPE = 'IN' AND C.TRANSACTION_TYPE = 'TR') OR
                  (C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE IN ('PAY', 'TR'))
            )
      GROUP BY P.ST_ID, C.TRANS_NUMBER, P.INVOICE_REFERENCE_NUM
UNION
      SELECT ID, OriginalTransaction, GiftReport.TransactionNumber, SourceSystem, TransactionDate,
    CASE
         WHEN SourceSystem = 'FR' THEN DateReceived
         ELSE TransactionDate END AS DateReceived,
      Amount, Fund, AppealCode, SolicitorID, CheckNumber, PaymentType, CampaignCode, FiscalYear, FiscalMonth,
    CASE
         WHEN SourceSystem = 'MEETING' THEN 'Event'
         ELSE GiftType END AS GiftType,
      InvoiceReferenceNumber, ReceiptID, MatchingTransaction, IsMatchingGift, MemorialID, PledgeID, ListAs,
      RequestNumber, InstallmentDate, MemorialNameText,
      GiftReport.MemorialTributeType,
      CONVERT(VARCHAR(512),GiftReport.MemorialTributeMessage),
      GiftReport.tributeNotificationContactID
      FROM GiftReport
            LEFT OUTER JOIN TransWatch ON
                  GiftReport.OriginalTransaction = TransWatch.TransactionNumber AND
                  GiftReport.InvoiceReferenceNumber = TransWatch.InvoiceNumber
      WHERE
            TransWatch.TransactionNumber IS NULL
UNION
      SELECT ID, OriginalTransaction, GiftReport.TransactionNumber, SourceSystem, TransactionDate,
      TransactionDate AS DateReceived,
      Amount, Fund, AppealCode, SolicitorID, CheckNumber, PaymentType, CampaignCode, FiscalYear, FiscalMonth,
     'Event' AS GiftType,
      InvoiceReferenceNumber, ReceiptID, MatchingTransaction, IsMatchingGift, MemorialID, PledgeID, ListAs,
      RequestNumber, InstallmentDate, MemorialNameText,
      GiftReport.MemorialTributeType,
      CONVERT(VARCHAR(512),GiftReport.MemorialTributeMessage),
      GiftReport.tributeNotificationContactID
      FROM GiftReport
      WHERE
            OriginalTransaction NOT IN (SELECT TransactionNumber FROM TransWatch) AND
            SourceSystem = 'MEETING'












GO